using System;
using System.Data;
using System.Data.OleDb;
using PCSComUtils.Common;
using PCSComUtils.DataAccess;
using PCSComUtils.PCSExc;

namespace PCSComUtils.Admin.DS
{
    public class Sys_RoleDS ////: IObjectDS
    {
        private const string THIS = "PCSComUtils.Admin.DS.DS.Sys_RoleDS";

        #region IObjectDS Members

        public void Add(object pobjObjectVO)
        {
            const string METHOD_NAME = THIS + ".Add()";

            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                var objObject = (Sys_RoleVO)pobjObjectVO;
                string strSql = String.Empty;
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand("", oconPCS);

                strSql = "INSERT INTO Sys_Role("
                         + Sys_RoleTable.NAME_FLD + ","
                         + Sys_RoleTable.DESCRIPTION_FLD + ")"
                         + "VALUES(?,?)";

                ocmdPCS.Parameters.Add(new OleDbParameter(Sys_RoleTable.NAME_FLD, OleDbType.Char));
                ocmdPCS.Parameters[Sys_RoleTable.NAME_FLD].Value = objObject.Name;

                ocmdPCS.Parameters.Add(new OleDbParameter(Sys_RoleTable.DESCRIPTION_FLD, OleDbType.VarWChar));
                ocmdPCS.Parameters[Sys_RoleTable.DESCRIPTION_FLD].Value = objObject.Description;


                ocmdPCS.CommandText = strSql;
                ocmdPCS.Connection.Open();
                ocmdPCS.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
                {
                    throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
                }
                else
                {
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                }
            }

            catch (InvalidOperationException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public void Delete(int pintID)
        {
            const string METHOD_NAME = THIS + ".Delete()";
            string strSql = String.Empty;
            strSql = "DELETE " + Sys_RoleTable.TABLE_NAME + " WHERE  " + "RoleID" + "=" + pintID;
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);

                ocmdPCS.Connection.Open();
                ocmdPCS.ExecuteNonQuery();
                ocmdPCS = null;
            }
            catch (OleDbException ex)
            {
                if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
                {
                    throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
                }
                else
                {
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                }
            }

            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }


            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public object GetObjectVO(int pintID)
        {
            const string METHOD_NAME = THIS + ".GetObjectVO()";
            var dstPCS = new DataSet();

            OleDbDataReader odrPCS = null;
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;
                strSql = "SELECT "
                         + Sys_RoleTable.ROLEID_FLD + ","
                         + Sys_RoleTable.NAME_FLD + ","
                         + Sys_RoleTable.DESCRIPTION_FLD
                         + " FROM " + Sys_RoleTable.TABLE_NAME
                         + " WHERE " + Sys_RoleTable.ROLEID_FLD + "=" + pintID;

                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);

                ocmdPCS.Connection.Open();
                odrPCS = ocmdPCS.ExecuteReader();

                var objObject = new Sys_RoleVO();

                while (odrPCS.Read())
                {
                    objObject.RoleID = int.Parse(odrPCS[Sys_RoleTable.ROLEID_FLD].ToString());
                    objObject.Name = odrPCS[Sys_RoleTable.NAME_FLD].ToString();
                    objObject.Description = odrPCS[Sys_RoleTable.DESCRIPTION_FLD].ToString();
                }
                return objObject;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }

            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public void Update(object pobjObjecVO)
        {
            const string METHOD_NAME = THIS + ".Update()";

            var objObject = (Sys_RoleVO)pobjObjecVO;


            //prepare value for parameters
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                strSql = "UPDATE Sys_Role SET "
                         + Sys_RoleTable.NAME_FLD + "=   ?" + ","
                         + Sys_RoleTable.DESCRIPTION_FLD + "=  ?"
                         + " WHERE " + Sys_RoleTable.ROLEID_FLD + "= ?";

                ocmdPCS.Parameters.Add(new OleDbParameter(Sys_RoleTable.NAME_FLD, OleDbType.Char));
                ocmdPCS.Parameters[Sys_RoleTable.NAME_FLD].Value = objObject.Name;

                ocmdPCS.Parameters.Add(new OleDbParameter(Sys_RoleTable.DESCRIPTION_FLD, OleDbType.VarWChar));
                ocmdPCS.Parameters[Sys_RoleTable.DESCRIPTION_FLD].Value = objObject.Description;

                ocmdPCS.Parameters.Add(new OleDbParameter(Sys_RoleTable.ROLEID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[Sys_RoleTable.ROLEID_FLD].Value = objObject.RoleID;


                ocmdPCS.CommandText = strSql;
                ocmdPCS.Connection.Open();
                ocmdPCS.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
                {
                    throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
                }
                else
                {
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                }
            }

            catch (InvalidOperationException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public DataSet List()
        {
            const string METHOD_NAME = THIS + ".List()";
            var dstPCS = new DataSet();


            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;

                strSql = "SELECT "
                         + Sys_RoleTable.ROLEID_FLD + ","
                         + Sys_RoleTable.NAME_FLD + ","
                         + Sys_RoleTable.DESCRIPTION_FLD
                         + " FROM " + Sys_RoleTable.TABLE_NAME
                         + " WHERE Name<>'" + Constants.ALL_ROLE + "'"
                         + " ORDER BY " + Sys_RoleTable.NAME_FLD;
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                var odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, Sys_RoleTable.TABLE_NAME);

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public void UpdateDataSet(DataSet pData)
        {
            const string METHOD_NAME = THIS + ".UpdateDataSet()";
            string strSql;
            OleDbConnection oconPCS = null;
            OleDbCommandBuilder odcbPCS;
            var odadPCS = new OleDbDataAdapter();
            DataSet dstChanged = pData.GetChanges();
            try
            {
                strSql = "SELECT "
                         + Sys_RoleTable.ROLEID_FLD + ","
                         + Sys_RoleTable.NAME_FLD + ","
                         + Sys_RoleTable.DESCRIPTION_FLD
                         + "  FROM " + Sys_RoleTable.TABLE_NAME
                         + " WHERE Name<>'" + Constants.ALL_ROLE + "'";

                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                oconPCS.Open();
                odadPCS.SelectCommand = new OleDbCommand(strSql, oconPCS);
                odcbPCS = new OleDbCommandBuilder(odadPCS);
                pData.EnforceConstraints = false;

                //odadPCS.Update(pData,Sys_RoleTable.TABLE_NAME);
                odadPCS.Update(dstChanged, Sys_RoleTable.TABLE_NAME);

                pData.Clear();
                odadPCS.Fill(pData, pData.Tables[0].TableName);
                pData.AcceptChanges();
            }
            catch (OleDbException ex)
            {
                if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
                {
                    throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
                }
                else if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
                {
                    throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
                }

                else
                {
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                }
            }

            catch (InvalidOperationException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        #endregion

        public DataSet ListAll()
        {
            const string METHOD_NAME = THIS + ".List()";
            var dstPCS = new DataSet();


            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;

                strSql = "SELECT "
                         + Sys_RoleTable.ROLEID_FLD + ","
                         + Sys_RoleTable.NAME_FLD + ","
                         + Sys_RoleTable.DESCRIPTION_FLD
                         + " FROM " + Sys_RoleTable.TABLE_NAME
                         + " ORDER BY " + Sys_RoleTable.NAME_FLD;
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                var odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, Sys_RoleTable.TABLE_NAME);

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public DataRow GetFieldLength()
        {
            const string METHOD_NAME = THIS + ".List()";
            var dstPCS = new DataSet();


            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;

                strSql = "SELECT COLUMNPROPERTY( OBJECT_ID('" + Sys_RoleTable.TABLE_NAME + "'),'" +
                         Sys_RoleTable.ROLEID_FLD + "','" + Constants.GET_FIELD_LENGTH + "')  as " +
                         Sys_RoleTable.ROLEID_FLD
                         + ", COLUMNPROPERTY( OBJECT_ID('" + Sys_RoleTable.TABLE_NAME + "'),'" + Sys_RoleTable.NAME_FLD +
                         "','" + Constants.GET_FIELD_LENGTH + "')  as " + Sys_RoleTable.NAME_FLD
                         + ", COLUMNPROPERTY( OBJECT_ID('" + Sys_RoleTable.TABLE_NAME + "'),'" +
                         Sys_RoleTable.DESCRIPTION_FLD + "','" + Constants.GET_FIELD_LENGTH + "')  as " +
                         Sys_RoleTable.DESCRIPTION_FLD;

                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                var odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, Sys_RoleTable.TABLE_NAME);

                return dstPCS.Tables[0].Rows[0];
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public DataSet ListRoleNotGrantToUser(int intUserId)
        {
            const string METHOD_NAME = THIS + ".ListRoleNotGrantToUser()";
            var dstPCS = new DataSet();


            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;

                strSql = "SELECT "
                         + Sys_RoleTable.ROLEID_FLD + ","
                         + Sys_RoleTable.NAME_FLD + ","
                         + Sys_RoleTable.DESCRIPTION_FLD
                         + " FROM " + Sys_RoleTable.TABLE_NAME
                         + " WHERE " + Sys_RoleTable.ROLEID_FLD + " NOT IN ("
                         + " SELECT " + Sys_UserToRoleTable.ROLEID_FLD
                         + " FROM  " + Sys_UserToRoleTable.TABLE_NAME
                         + " WHERE " + Sys_UserToRoleTable.USERID_FLD + " = " + intUserId + ") AND Name<>'" +
                         Constants.ALL_ROLE + "'";

                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                var odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, Sys_RoleTable.TABLE_NAME);

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public DataSet ListRoleAndSysParam(int pintUserID, string pstrUserName)
        {
            const string METHOD_NAME = THIS + ".ListRoleAndSysParam()";
            var dstPCS = new DataSet();

            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS;
            try
            {
                string strSql = "SELECT "
                                + Sys_RoleTable.ROLEID_FLD + ","
                                + Sys_RoleTable.NAME_FLD + ","
                                + Sys_RoleTable.DESCRIPTION_FLD
                                + " FROM " + Sys_RoleTable.TABLE_NAME
                                + " WHERE " + Sys_RoleTable.ROLEID_FLD + " IN ("
                                + " SELECT " + Sys_UserToRoleTable.ROLEID_FLD
                                + " FROM  " + Sys_UserToRoleTable.TABLE_NAME
                                + " WHERE " + Sys_UserToRoleTable.USERID_FLD + " = " + pintUserID + ")";
                strSql += "; SELECT "
                          + " CCN." + MST_CCNTable.CCNID_FLD + ","
                          + " CCN." + MST_CCNTable.CODE_FLD + ","
                          + " CCN." + MST_CCNTable.DESCRIPTION_FLD + ","
                          + " CCN." + MST_CCNTable.NAME_FLD + ","
                          + " CCN." + MST_CCNTable.STATE_FLD + ","
                          + " CCN." + MST_CCNTable.ZIPCODE_FLD + ","
                          + " CCN." + MST_CCNTable.PHONE_FLD + ","
                          + " CCN." + MST_CCNTable.FAX_FLD + ","
                          + " CCN." + MST_CCNTable.WEBSITE_FLD + ","
                          + " CCN." + MST_CCNTable.EMAIL_FLD + ","
                          + " CCN." + MST_CCNTable.VAT_FLD + ","
                          + " CCN." + MST_CCNTable.COUNTRYID_FLD + ","
                          + " CCN." + MST_CCNTable.CITYID_FLD + ","
                    //+ " CCN." + MST_CCNTable.CHARTOFACCOUNTSTRUCTID_FLD + ","
                          + " CCN." + MST_CCNTable.HOMECURRENCYID_FLD + ","
                          + " Cur." + MST_CurrencyTable.CODE_FLD + " HomeCurrency,"
                          + " CCN." + MST_CCNTable.EXCHANGERATE_FLD + ","
                          + " CCN." + MST_CCNTable.DEFAULTCURRENCYID_FLD + ","
                          + " CC." + MST_CurrencyTable.CODE_FLD + " DefaultCurrency,"
                          + " EM." + MST_EmployeeTable.EMPLOYEEID_FLD + ","
                          + " EM." + MST_EmployeeTable.CODE_FLD + " EmployeeName,"
                          + " US." + Sys_UserTable.MASTERLOCATIONID_FLD + ","
                          + " M." + MST_MasterLocationTable.CODE_FLD + " MasterLocationCode,"
                          + " M." + MST_MasterLocationTable.NAME_FLD + " MasterLocationName,"
                          + " CCN." + MST_CCNTable.EXCHANGERATEOPERATOR_FLD
                          + " FROM " + MST_CCNTable.TABLE_NAME
                          + " CCN LEFT JOIN " + MST_CurrencyTable.TABLE_NAME + " Cur on Cur."
                          + MST_CurrencyTable.CURRENCYID_FLD + " = CCN." + MST_CCNTable.HOMECURRENCYID_FLD
                          + " LEFT JOIN " + MST_CurrencyTable.TABLE_NAME + " CC on CC."
                          + MST_CurrencyTable.CURRENCYID_FLD + " = CCN." + MST_CCNTable.DEFAULTCURRENCYID_FLD
                          + " INNER JOIN " + Sys_UserTable.TABLE_NAME + " US ON CCN."
                          + MST_CCNTable.CCNID_FLD + " = " + "US." + Sys_UserTable.CCNID_FLD
                          + " INNER JOIN " + MST_EmployeeTable.TABLE_NAME + " EM ON EM." +
                          MST_EmployeeTable.EMPLOYEEID_FLD
                          + " = US." + MST_EmployeeTable.EMPLOYEEID_FLD
                          + " INNER JOIN " + MST_MasterLocationTable.TABLE_NAME + " M ON M." +
                          MST_MasterLocationTable.MASTERLOCATIONID_FLD
                          + " = US." + Sys_UserTable.MASTERLOCATIONID_FLD
                          + " WHERE " + " US." + Sys_UserTable.USERID_FLD + "=" + pintUserID;
                          //+ " AND CCN." + MST_CCNTable.CCNID_FLD + "=" + pintCCNID;
                strSql += "; SELECT "
                          + Sys_ParamTable.PARAMID_FLD + ","
                          + Sys_ParamTable.NAME_FLD + ","
                          + Sys_ParamTable.VALUE_FLD
                          + " FROM " + Sys_ParamTable.TABLE_NAME;

                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                var odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, Sys_RoleTable.TABLE_NAME);

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public DataSet ListRoleGrantToUser(int intUserId)
        {
            const string METHOD_NAME = THIS + ".ListRoleNotGrantToUser()";
            var dstPCS = new DataSet();

            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;

                strSql = "SELECT "
                         + Sys_RoleTable.ROLEID_FLD + ","
                         + Sys_RoleTable.NAME_FLD + ","
                         + Sys_RoleTable.DESCRIPTION_FLD
                         + " FROM " + Sys_RoleTable.TABLE_NAME
                         + " WHERE " + Sys_RoleTable.ROLEID_FLD + " IN ("
                         + " SELECT " + Sys_UserToRoleTable.ROLEID_FLD
                         + " FROM  " + Sys_UserToRoleTable.TABLE_NAME
                         + " WHERE " + Sys_UserToRoleTable.USERID_FLD + " = " + intUserId + ")";

                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                var odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, Sys_RoleTable.TABLE_NAME);

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public DataSet ListRoleGrantToUser(string pstrUserName)
        {
            const string METHOD_NAME = THIS + ".ListRoleNotGrantToUser()";
            var dstPCS = new DataSet();

            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;

                strSql = "SELECT "
                         + Sys_UserToRoleTable.ROLEID_FLD + ","
                         + " FROM " + Sys_UserToRoleTable.TABLE_NAME
                         + " WHERE " + Sys_UserToRoleTable.USERID_FLD + " IN ("
                         + " SELECT " + Sys_UserTable.USERID_FLD
                         + " FROM  " + Sys_UserTable.TABLE_NAME
                         + " WHERE " + Sys_UserTable.USERNAME_FLD + " = '" + pstrUserName + "' )";

                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                var odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, Sys_UserToRoleTable.TABLE_NAME);

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public bool UserNameBelongToAdministratorRole(string pstrUserName)
        {
            const string METHOD_NAME = THIS + ".UserNameBelongToAdministratorRole()";

            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;

                strSql = "Select count(*) from sys_role r"
                         + " inner join sys_usertoRole ur on r.roleid=ur.roleid"
                         + " inner join sys_user u on ur.Userid=u.UserID"
                         + " where u.UserName = ' + pstrUserName + '"
                         + " and r.Name = '" + Constants.ADMINISTRATORS + "'";

                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();
                object objResult = ocmdPCS.ExecuteScalar();
                if (objResult != null)
                {
                    if (int.Parse(objResult.ToString()) > 0)
                        return true;
                    else
                        return false;
                }
                else
                {
                    return false;
                }
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (PCSException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }
            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public DataSet GetRightToModify(string pstrUserName, string pstrTableName, string pstrPrimaryKeyField,
                                        int pintMasterID)
        {
            var dstPCS = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;

            try
            {
                string strSql = String.Empty;
                // Get right belong to admin role to modify
                strSql = "Select ISNULL(count(*),0) from sys_role r"
                         + " inner join sys_usertoRole ur on r.roleid=ur.roleid"
                         + " inner join sys_user u on ur.Userid=u.UserID"
                         + " where u.UserName = '" + pstrUserName + "'"
                         + " and r.Name = '" + Constants.ADMINISTRATORS + "'";
                // Get created user (UserName)
                strSql += "; Select ISNULL(UserName,'') UserName From " + pstrTableName
                          + " Where " + pstrPrimaryKeyField + " = " + pintMasterID;

                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                var odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS);


                return dstPCS;
            }
            catch
            {
                return null;
            }
            // Don't show message if the form not open from menu
            //			catch(OleDbException ex)
            //			{			
            //				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
            //			}			
            //			catch(PCSException ex)
            //			{
            //				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
            //			}
            //			catch (Exception ex) 
            //			{
            //				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            //			}
            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public string GetCreatedUser(string pstrTableName, string pstrPrimaryKeyField, int pintMasterID)
        {
            const string METHOD_NAME = THIS + ".UserNameBelongToAdministratorRole()";

            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;

                strSql = "Select UserName From " + pstrTableName + " TranTable"
                         + " Where TranTable." + pstrPrimaryKeyField + " = " + pintMasterID;

                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();
                object objResult = ocmdPCS.ExecuteScalar();
                return objResult.ToString();
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (PCSException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }
            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public DataSet GetSecurityInfo(string pstrUserName, string pstrFormName)
        {
            var dstPCS = new DataSet();


            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;
                // Get Role by user
                strSql = "SELECT "
                         + Sys_UserToRoleTable.ROLEID_FLD
                         + " FROM " + Sys_UserToRoleTable.TABLE_NAME
                         + " WHERE " + Sys_UserToRoleTable.USERID_FLD
                         + " IN ( SELECT " + Sys_UserTable.USERID_FLD + " FROM " + Sys_UserTable.TABLE_NAME
                         + " WHERE " + Sys_UserTable.USERNAME_FLD + " = '" + pstrUserName + "' ); ";
                // Get Permistion base on FormName, and list of role
                strSql += "Select IsNull(Sum(Permission),0) Permission "
                          + " From Sys_Right "
                          + " Where Menu_EntryID IN (Select Menu_EntryID From Sys_Menu_Entry Where FormLoad='" +
                          pstrFormName + "') "
                          + " AND RoleID IN (Select RoleID From Sys_UserToRole "
                          + " WHERE UserID = (Select UserID From Sys_User Where UserName='" + pstrUserName + "')); ";
                // Get Existed record menu
                strSql += " Select IsNull(Count(*),0) ExistedForm From Sys_menu_entry Where FormLoad = '" + pstrFormName +
                          "'; ";
                // GetVisibleControl
                strSql += " SELECT VG.FormName,VI.Name ControlName, ISNULL(VI.Type,0) Type"
                          + " FROM sys_VisibilityGroup_Role VGR"
                          + " INNER JOIN sys_VisibilityGroup VG ON VGR.GroupID=VG.VisibilityGroupID"
                          + " INNER JOIN sys_VisibilityItem VI ON VG.VisibilityGroupID=VI.GroupID"
                          + " WHERE VG.FormName = '" + pstrFormName + "'"
                          + " AND VGR.RoleID IN (Select RoleID From Sys_UserToRole "
                          + " WHERE UserID = (Select UserID From Sys_User Where UserName='" + pstrUserName + "') )";


                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                var odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS);
                dstPCS.Tables[0].TableName = Sys_UserToRoleTable.TABLE_NAME;
                dstPCS.Tables[1].TableName = Sys_RightTable.TABLE_NAME;
                dstPCS.Tables[2].TableName = Sys_Menu_EntryTable.TABLE_NAME;
                dstPCS.Tables[3].TableName = Sys_VisibilityGroup_RoleTable.TABLE_NAME;

                return dstPCS;
            }
            catch
            {
                return null;
            }

//			catch(OleDbException ex)
            //			{
            //				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
            //			}			
            //			catch (Exception ex) 
            //			{
            //				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            //			}

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public void UpdateUserNameModifyTransaction(string pstrUserName, string pstrTableName,
                                                    string pstrPrimaryKeyField, int pintMasterID)
        {
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;

                strSql = "Update " + pstrTableName + " SET UserName = '" + pstrUserName + "', LastChange = GetDate()"
                         + " Where " + pstrPrimaryKeyField + " = " + pintMasterID;

                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();
                ocmdPCS.ExecuteScalar();
            }
            catch
            {
                // do nothing
            }
            // Don't show error message if the form was opened from other form.
            //			catch(OleDbException ex)
            //			{			
            //				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
            //			}			
            //			catch(PCSException ex)
            //			{
            //				throw ex;
            //			}
            //			catch (Exception ex) 
            //			{
            //				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            //			}
            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public DataSet GetDefaultInfomation()
        {
            const string METHOD_NAME = THIS + ".GetDefaultInfomation()";
            var dstPCS = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;

            try
            {
                string strSql = "SELECT "
                                + Sys_Error_MsgTable.ERROR_MSGID_FLD + ","
                                + Sys_Error_MsgTable.CODE_FLD + ","
                                + Sys_Error_MsgTable.MSGDEFAULT_FLD + ","
                                + Sys_Error_MsgTable.MSGVN_FLD + ","
                                + Sys_Error_MsgTable.MSGEN_FLD + ","
                                + Sys_Error_MsgTable.MSGJP_FLD + ","
                                + Sys_Error_MsgTable.DESCRIPTION_FLD
                                + " FROM " + Sys_Error_MsgTable.TABLE_NAME;
                // Get Sys_menu_entry
                strSql += "; Select Parent_Shortcut, Menu_EntryID, Shortcut, Button_Caption, Text_CaptionDefault, Text_Caption_VI_VN, "
                          +
                          " Text_Caption_EN_US, Text_Caption_JA_JP, Text_Caption_Language_Default, Parent_Child, FormLoad, Description, Type, "
                          +
                          " CollapsedImage, ExpandedImage, Prefix, TransFormat, IsTransaction, IsUserCreated, TableName, TransNoFieldName, ReportID"
                          + " FROM " + Sys_Menu_EntryTable.TABLE_NAME;

                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                var odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS);
                dstPCS.Tables[0].TableName = Sys_Error_MsgTable.TABLE_NAME;
                dstPCS.Tables[1].TableName = Sys_Menu_EntryTable.TABLE_NAME;

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (PCSException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }
            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }
    }
}